package MaintMenu;

import CreateNewItem.SQLEXEC;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * User: Jason
 * Date: 8/7/13
 * Time: 10:30 PM
 * To change this template use File | Settings | File Templates.
 */

// this is for verify and modify CAPA if needed
public class VendorCostPreProcess {
    String tbl="";
    String pl="";
    String charc="";
    char lastC;
    int totalfiles=1;
    String isCapa="";
    boolean capa;
    String oPL="";
    int pkg=0;
    Double cuft=0.00D;
    String last2Co="";
    String last2C="";
    String orgItem ="";
    public VendorCostPreProcess(String vendor, int filesToUpdate){

        if (vendor=="DP"){
            tbl="_costdepo";

        }else
        if (vendor=="TW"){
            tbl="_costtygtw";
        }else
        if (vendor=="YCC"){
            tbl="_costYCC";
        }else
        if (vendor=="GN"){
            tbl="_costgenera";
        }

        while (totalfiles<filesToUpdate+1){
            //System.out.print("Processing"+totalfiles+"\t\t");


            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                //Connection to Hicksville
                String dataSourceName = "dbLI";
                String dbURL = "jdbc:odbc:" + dataSourceName;
                Connection connection = DriverManager.getConnection(dbURL, "", "");
                Connection connection1 = DriverManager.getConnection(dbURL, "", "");
                Statement stmt1 = connection.createStatement();
                DatabaseMetaData md = connection1.getMetaData();
//                Statement stmt2 = connection.createStatement();
                Statement stmtUpd = connection.createStatement();
                Statement stmtAlt = connection.createStatement();
                Statement stmtc = connection.createStatement();
                Statement stmtd = connection.createStatement();
    //            Statement stmt1 = connection.createStatement();

                if (vendor=="DP"){
                ResultSet rs = md.getColumns(null, null, tbl+totalfiles, "partslink");
                    if (rs.next()) {
                        //Column in table exist
    //                    System.out.println("column exist");
                    }else {
    //                    System.out.println("column not exist");
                        stmtAlt.execute("alter table "+tbl+totalfiles+" add PARTSLINK CHAR(10)");
                        stmtAlt.execute("alter table "+tbl+totalfiles+" add CERT CHAR(3)");
                        stmtAlt.execute("alter table "+tbl+totalfiles+" add  CAPA YESNO");
                    }
                stmtAlt.close();
                connection1.close();
                }
                else if (vendor=="YCC"){
                    ResultSet rs = md.getColumns(null, null, tbl+totalfiles, "orgCuft");
                    if (rs.next()) {
                        stmtAlt.execute("UPDATE "+tbl+totalfiles+" SET orgCuft=cuft");
                        //                    System.out.println("column exist");
                    }else {
                        //                    System.out.println("column not exist");
//                        stmtAlt.execute("alter table "+tbl+totalfiles+" add PARTSLINK CHAR(10)");
                        stmtAlt.execute("alter table "+tbl+totalfiles+" add orgCuft DOUBLE ");
                        stmtAlt.execute("UPDATE "+tbl+totalfiles+" SET orgCuft=cuft");
                    }
                }
                else if (vendor=="TW"){
                    ResultSet rs = md.getColumns(null, null, tbl+totalfiles, "orgCuft");
                    if (rs.next()) {
                        stmtAlt.execute("UPDATE "+tbl+totalfiles+" SET orgCuft=[cu/ft]");
                        //                    System.out.println("column exist");
                    }else {
                        //                    System.out.println("column not exist");
//                        stmtAlt.execute("alter table "+tbl+totalfiles+" add PARTSLINK CHAR(10)");
                        stmtAlt.execute("alter table "+tbl+totalfiles+" add orgCuft DOUBLE ");
                        stmtAlt.execute("UPDATE "+tbl+totalfiles+" SET orgCuft=[cu/ft]");
                    }
                    stmtAlt.close();
                    connection1.close();
                }
                else if (vendor=="GN"){
                    ResultSet rs = md.getColumns(null, null, tbl+totalfiles, "CAPA");
                    if (rs.next()) {
                        stmtAlt.execute("update "+tbl+totalfiles+" set capa=false");
                        //                    System.out.println("column exist");
                    }else {
                        //                    System.out.println("column not exist");
//                        stmtAlt.execute("alter table "+tbl+totalfiles+" add PARTSLINK CHAR(10)");
                        stmtAlt.execute("alter table "+tbl+totalfiles+" add CAPA yesno ");
                        stmtAlt.execute("UPDATE "+tbl+totalfiles+" SET orgCuft=[cu/ft]");
                    }
                    stmtAlt.close();
                    connection1.close();
                }


                String selecSta1 ="SELECT * FROM "+tbl+totalfiles ;
//                System.out.println(tbl+totalfiles);
    //                String selecSta ="SELECT * FROM "+tbl+"";
    //            System.out.println("Statement :"+selecSta);



                stmt1.execute(selecSta1);

                ResultSet resultSet1 = stmt1.getResultSet();


                int rowupd =0;
                if (resultSet1 != null)  {
                    if (vendor=="DP"){
                        stmtUpd.executeUpdate("UPDATE "+tbl+totalfiles+" AS a INNER JOIN _CostDEPOupdate AS b ON a.[depo no] = b.[depo no] SET a.[Unit Price] = b.[new net $];");
                        //System.out.println("new price updated for  "+tbl+totalfiles);
                    }


                    while (resultSet1.next()) {
                        rowupd++;

                        //Following is used to update Depo Pricing                                                ******


                        if (vendor=="DP"){

                            pl= resultSet1.getString("partslink1");

                            //**************** determine capa, (nsf) or not non capa
                            //if no partlink# found in the record
                            if (pl==null){
                                pl="NA";
                            }
                            charc=pl.substring(pl.length() - 1);
                            lastC=charc.charAt(0);
                            if (lastC=='C'){
                                isCapa="Y";
    //                            System.out.println("found capa"+isCapa);


                            }else if (lastC=='N'){

                                isCapa="NSF";

                            }
                            else {
                                isCapa="N";
                            }
                            //***************
    //                        System.out.println(pl.length());
    //                        isCapa=((pl.length()-1)=='C');


    //                        if(pl.length()>9 && isCapa);      {
                            //non CAPA
                            if(isCapa=="N") {
                                capa=false;


    //                            System.out.println("not capa :"+lastC);
                                String sql="update "+tbl+totalfiles+" set partslink= '"+pl+"', cert='"+isCapa+"', capa="+capa+" where partslink1="+"'"+pl+"'";
//                                System.out.println(sql);
                                stmtc.executeUpdate(sql);



                                //may include C for CAPA
    //                            System.out.println("PL #:"+pl+" is Capa:"+isCapa);


                                //CAPA or NSF
                            }else if (isCapa=="Y" || isCapa=="NSF"){

                                if (isCapa=="Y"){
                                    capa=true;
                                }else  if (isCapa=="NSF"){
                                    capa=false;
                                }

    //                            System.out.println("process capa and nsf items");
    //                            System.out.println("PL #:"+pl+" is Capa:"+isCapa);
    //                            System.out.println(" capa "+lastC);
    //                            System.out.print(pl + " ");
                                //delete last character

                                oPL=pl;
                                pl = pl.substring(0, pl.length() - 1);
    //                            System.out.println(pl);
                                String sql="update "+tbl+totalfiles+" set partslink= '"+pl+"', cert='"+isCapa+"', capa="+capa+" where partslink1="+"'"+oPL+"'";
//                                System.out.println(sql);
                                stmtc.executeUpdate(sql);
                            }
                        }
                        if (vendor=="TW"){
                            //DELETE ALL OEM#
//                            String sql="update "+tbl+totalfiles+" set [OE#]='NA'";
////                                                        System.out.println(sql);
//                            stmtc.executeUpdate(sql);
                            pl= resultSet1.getString("PARTSLINK");
                            pkg = resultSet1.getInt("STD QTY");
                            cuft = resultSet1.getDouble("CU/FT");
                            cuft= (cuft/pkg);
                            String sql="update "+tbl+totalfiles+" set [cu/ft] = "+cuft+" where partslink="+"'"+pl+"'";
                            //                            System.out.println(sql);
                            stmtc.executeUpdate(sql);



                        }if (vendor=="YCC"){            //replace package cuft with new single cuft
                            pl= resultSet1.getString("PARTLINK");
                            pkg = resultSet1.getInt("PCS/CTN");
                            cuft = resultSet1.getDouble("CUFT");
                            cuft= (cuft/pkg);
                            String sql="update "+tbl+totalfiles+" set cuft = "+cuft+" where partlink="+"'"+pl+"'";
                            //                            System.out.println(sql);
                            stmtc.executeUpdate(sql);

                        }
                        if (vendor=="GN"){            //replace package cuft with new single cuft
                            pl= resultSet1.getString("PARTSLINK #");
                            last2Co = resultSet1.getString("ITEM");
                            orgItem=last2Co;

//                            String substring = str.substring(Math.max(str.length() - 2, 0));
                            last2C=last2Co.substring(Math.max(last2Co.length() - 2, 0));

                            if(last2C.equals("-9")){
//                                System.out.println(last2Co);
//                            cuft = resultSet1.getDouble("CUFT");
//                            cuft= (cuft/pkg);
                            String sql="update "+tbl+totalfiles+" set capa=yes  where item ="+"'"+orgItem+"'";
//                                System.out.println(sql);
                            //                            System.out.println(sql);
                            stmtc.executeUpdate(sql);
                            }else{
                                String sql="update "+tbl+totalfiles+" set capa=false  where [partslink #]="+"'"+pl+"'";
//                                System.out.println(last2Co+ " not capa");
                            }
                        }
//                        pl="";
//                        last2Co="";
//                        last2C="";
    //                    System.out.println("LPP for "+partnum+" is :"+lpp);
                    }
                }
                totalfiles++;
                //close connection and statements
                connection.close();
                connection1.close();
                stmt1.close();
//                stmt2.close();
                stmtAlt.close();
                stmtc.close();
                stmtUpd.close();
//                System.out.println("close");
            }catch (ClassNotFoundException e) {
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                System.out.println("1:"+e);
            } catch (SQLException e) {
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                System.out.println("2:"+e);
            }
        }
    }
}
